/*/{Protheus.doc} SYNCSAL
E a User Function de geracao do layout SAL (Saldos Contabeis) do Sistema Synchro
@author Elton Teodoro Alves
@since 14/11/2014
@version P11 R5
@Param aParam,Array,Array enviado pelo schudele
@Example
Esta User Function foi desenvolvida para ser utilizada com o Schudele, que passa como parametro para User Function um array com a seguinte configuracao:</br>
</br>
<table>
<tr><th> POSICAO </th><th> TIPO </th><th> DESCRICAO             </th></tr>
<tr><td> [01]    </td><td> C    </td><td> Codigo da Empresa     </td></tr>
<tr><td> [02]    </td><td> C    </td><td> Codigo da Filial      </td></tr>
<tr><td> [03]    </td><td> C    </td><td> Codigo do Agendamento </td></tr>
<tr><td> [04]    </td><td> C    </td><td> Codigo da Tarefa      </td></tr>
</table>
</br>
Para teste fora do schudele, pode-se enviar para o SmartClient com a clausula -a estes dados separadas por virgula ',' Ex.: 99,01,0001,0001
/*/

#Define CRLF Chr(13) + Chr(10)
Static PERIOD := 'M'

#Include 'Protheus.ch'

User Function SYNCSAL(xParam)
	
	Local	cLayout		:=	Right(ProcName(),3)
	Local	aParam		:=	If(Valtype(xParam) == 'C',Separa(xParam,',',.T.),xParam)
	Local	aSyncEnv	:=	U_SYNCENV(aParam,cLayout,PERIOD)
	Local	cTrab		:=	GetNextAlias()
	Local	cTempTrab	:=	cLayout + Dtos(Date()) + StrTran(Time(),':','')
	Local	cTempFile	:=	GetSrvProfString("StartPath","") + cTempTrab + GetDbExtension()
	Local	cQueryCT2D	:=	''
	Local	cQueryCT2C	:=	''
	Local	cQueryCT2S	:=	''
	Local	cQuery		:=	''
	Local	cQueryBkp	:=	''
	Local	aStru		:=	{}
	Local	nX			:=	0
	Local	aFiliais	:=	StrTokArr(AllTrim(aSyncEnv[10]),'/',)
	Local	cFiliais	:=	''
	Local	nCount		:=	0
	Local	cPriDiaMes	:=	aSyncEnv[9] + '01'
	Local	cUltDiaMes	:=	DtoS(LastDate(StoD(cPriDiaMes)))
	
	// Monta a string com a lista de filiais para ser utilizada na query
	
	For nX := 1 To Len(aFiliais)
		
		cFiliais += "'" + aFiliais[nX] + "'"
		
		If nX # Len(aFiliais)
			
			cFiliais += ","
			
		End If
		
	Next nX
	
	//Define o ultimos dia de cada Mes e do Mes de Referencia
	
	// --------------------------------------------------------------
	// Monta estrutura do arquivo temporario com dados a serem exportados
	// --------------------------------------------------------------
	aAdd(aStru,{'POS0001','C',0006,0}) // CHAVE_LOTE
	aAdd(aStru,{'POS0007','C',0010,0}) // ORIGEM
	aAdd(aStru,{'POS0017','C',0040,0}) // CHAVE_ORIGEM
	aAdd(aStru,{'POS0057','C',0001,0}) // CTRL_INSTRUCAO
	aAdd(aStru,{'POS0058','C',0018,0}) // CODIGO_EST
	aAdd(aStru,{'POS0076','C',0008,0}) // MES_ANO
	aAdd(aStru,{'POS0084','C',0015,0}) // CODIGO_ANALITICA
	aAdd(aStru,{'POS0099','C',0021,0}) // VL_INICIAL
	aAdd(aStru,{'POS0120','C',0001,0}) // IND_DB_CR
	aAdd(aStru,{'POS0121','C',0021,0}) // VL_DEBITO
	aAdd(aStru,{'POS0142','C',0021,0}) // VL_CREDITO
	aAdd(aStru,{'POS0163','C',0021,0}) // VL_SALDO_FINAL_ACUMULADO
	aAdd(aStru,{'POS0184','C',0001,0}) // IND_DB_CR_SLD_FINAL
	aAdd(aStru,{'POS0185','C',0028,0}) // CODIGO_CUSTO
	aAdd(aStru,{'POS0213','C',0022,0}) // CONT_CODIGO_CUSTO
	
	// --------------------------------------------------------------
	
	If aSyncEnv[6]
		
		// --------------------------------------------------------------
		// Montagem da query de pesquisa
		// --------------------------------------------------------------
		
		cQueryCT2D += "SELECT " + CRLF
		cQueryCT2C += "SELECT " + CRLF
		cQueryCT2S += "SELECT " + CRLF
		
		cQueryCT2S += "CT2S.POS0001," + CRLF
		cQueryCT2D += "'" + cPriDiaMes + "' POS0001," + CRLF
		cQueryCT2C += "'" + cPriDiaMes + "' POS0001," + CRLF
		
		cQueryCT2S += "CT2S.POS0007," + CRLF
		cQueryCT2D += "CT2D.CT2_ITEMD POS0007," + CRLF
		cQueryCT2C += "CT2C.CT2_ITEMC POS0007," + CRLF
		
		cQueryCT2S += "CT2S.POS0017," + CRLF
		cQueryCT2D += "CT2D.CT2_ITEMD POS0017," + CRLF
		cQueryCT2C += "CT2C.CT2_ITEMC POS0017," + CRLF
		
		cQueryCT2S += "CT2S.POS0057," + CRLF
		cQueryCT2D += "'I' POS0057," + CRLF
		cQueryCT2C += "'I' POS0057," + CRLF
		
		cQueryCT2S += "CT2S.POS0058," + CRLF
		cQueryCT2D += "CT2D.CT2_ITEMD POS0058," + CRLF
		cQueryCT2C += "CT2C.CT2_ITEMC POS0058," + CRLF
		
		cQueryCT2S += "CT2S.POS0076," + CRLF
		cQueryCT2D += "'" + cPriDiaMes + "' POS0076," + CRLF
		cQueryCT2C += "'" + cPriDiaMes + "' POS0076," + CRLF
		
		cQueryCT2S += "CT2S.POS0084," + CRLF
		cQueryCT2D += "SUBSTRING(CT2D.CT2_DEBITO,7,7) POS0084," + CRLF
		cQueryCT2C += "SUBSTRING(CT2C.CT2_CREDIT,7,7) POS0084," + CRLF
		
		//-------- Saldo Anterior
		
		cQueryCT2S += "SUM(CT2S.POS0099) POS0099," + CRLF
		
		cQueryCT2D += "CASE WHEN CT2D.CT2_DATA < '" + cPriDiaMes + "' THEN -CT2D.CT2_VALOR" + CRLF
		cQueryCT2D += "ELSE 0" + CRLF
		cQueryCT2D += "END POS0099," + CRLF
		
		cQueryCT2C += "CASE WHEN CT2C.CT2_DATA < '" + cPriDiaMes + "' THEN +CT2C.CT2_VALOR" + CRLF
		cQueryCT2C += "ELSE 0" + CRLF
		cQueryCT2C += "END POS0099," + CRLF
		
		//-------- D/C Saldo Anterior
		
		cQueryCT2S += "CASE WHEN SUM(CT2S.POS0099) >= 0 THEN 'C'" + CRLF
		cQueryCT2S += "ELSE 'D'" + CRLF
		cQueryCT2S += "END POS0120," + CRLF
		
		cQueryCT2D += "'' POS0120," + CRLF
		cQueryCT2C += "'' POS0120," + CRLF
		
		//-------- Saldo Debitos
		
		cQueryCT2S += "SUM(CT2S.POS0121) POS0121," + CRLF
		
		cQueryCT2D += "CASE WHEN CT2D.CT2_DATA >= '" + cPriDiaMes + "' AND  CT2D.CT2_DATA <= '" + cUltDiaMes + "' THEN +CT2D.CT2_VALOR" + CRLF
		cQueryCT2D += "ELSE 0" + CRLF
		cQueryCT2D += "END POS0121," + CRLF
		
		cQueryCT2C += "0 POS0121," + CRLF
		
		//-------- Saldo Creditos
		
		cQueryCT2S += "SUM(CT2S.POS0142) POS0142," + CRLF
		
		cQueryCT2D += "0 POS0142," + CRLF
		
		cQueryCT2C += "CASE WHEN CT2C.CT2_DATA >= '" + cPriDiaMes + "' AND  CT2C.CT2_DATA <= '" + cUltDiaMes + "' THEN +CT2C.CT2_VALOR" + CRLF
		cQueryCT2C += "ELSE 0" + CRLF
		cQueryCT2C += "END POS0142," + CRLF
		
		//-------- Saldo Atual
		
		cQueryCT2S += "SUM(CT2S.POS0163) POS0163," + CRLF
		
		cQueryCT2D += "CASE WHEN CT2D.CT2_DATA <= '" + cUltDiaMes + "' THEN -CT2D.CT2_VALOR" + CRLF
		cQueryCT2D += "ELSE 0" + CRLF
		cQueryCT2D += "END POS0163," + CRLF
		
		cQueryCT2C += "CASE WHEN CT2C.CT2_DATA <= '" + cUltDiaMes + "' THEN +CT2C.CT2_VALOR" + CRLF
		cQueryCT2C += "ELSE 0" + CRLF
		cQueryCT2C += "END POS0163," + CRLF
		
		//-------- D/C Saldo Atual
		
		cQueryCT2S += "CASE WHEN SUM(CT2S.POS0163) >= 0 THEN 'C'" + CRLF
		cQueryCT2S += "ELSE 'D'" + CRLF
		cQueryCT2S += "END POS0184," + CRLF
		
		cQueryCT2D += "'' POS0184," + CRLF
		cQueryCT2C += "'' POS0184," + CRLF
		
		cQueryCT2S += "CT2S.POS0185," + CRLF
		cQueryCT2D += "ISNULL(ZB4D.ZB4_CCUSTO,'') POS0185," + CRLF
		cQueryCT2C += "ISNULL(ZB4C.ZB4_CCUSTO,'') POS0185," + CRLF
		
		cQueryCT2S += "CT2S.POS0213" + CRLF
		cQueryCT2D += "'' POS0213" + CRLF
		cQueryCT2C += "'' POS0213" + CRLF
		
		//---- From CT2D CT2C
		
		cQueryCT2D += "FROM " + RetSqlName("CT2") + " CT2D " + CRLF
		cQueryCT2C += "FROM " + RetSqlName("CT2") + " CT2C " + CRLF
		
		//---- LEFT JOIN CT2D CT2C
		
		cQueryCT2D += "LEFT JOIN " + RetSqlName("ZB4") + " ZB4D ON " + CRLF
		cQueryCT2D += "RTRIM(LTRIM(CT2D.CT2_ITEMD)) = ZB4D.ZB4_FILIAL AND " + CRLF
		cQueryCT2D += "CT2D.CT2_CCD = ZB4D.ZB4_CCUSTO " + CRLF
		
		cQueryCT2C += "LEFT JOIN " + RetSqlName("ZB4") + " ZB4C ON " + CRLF
		cQueryCT2C += "RTRIM(LTRIM(CT2C.CT2_ITEMC)) = ZB4C.ZB4_FILIAL AND " + CRLF
		cQueryCT2C += "CT2C.CT2_CCC = ZB4C.ZB4_CCUSTO " + CRLF
		
		//---- WHERE CT2D CT2C
		
		cQueryCT2D += "WHERE CT2D.D_E_L_E_T_ <> '*' AND " + CRLF
		cQueryCT2D += "CT2D.CT2_DC IN('1','2','3') AND " + CRLF
		cQueryCT2D += "CT2D.CT2_TPSALD = '1' AND " + CRLF
		cQueryCT2D += "CT2D.CT2_DATA <= '" + cUltDiaMes + "' AND " + CRLF
		cQueryCT2D += "CT2D.CT2_DEBITO <> '" + Space(GetSx3Cache('CT2_DEBITO','X3_TAMANHO')) + "' AND " + CRLF
		cQueryCT2D += "CT2D.CT2_ITEMD IN(" + cFiliais + ") AND "
		cQueryCT2D += "ISNULL(ZB4D.D_E_L_E_T_,' ') <> '*'"
		
		cQueryCT2C += "WHERE CT2C.D_E_L_E_T_ <> '*' AND " + CRLF
		cQueryCT2C += "CT2C.CT2_DC IN('1','2','3') AND " + CRLF
		cQueryCT2C += "CT2C.CT2_TPSALD = '1' AND " + CRLF
		cQueryCT2C += "CT2C.CT2_DATA <= '" + cUltDiaMes + "' AND " + CRLF
		cQueryCT2C += "CT2C.CT2_CREDIT <> '" + Space(GetSx3Cache('CT2_CREDIT','X3_TAMANHO')) + "' AND " + CRLF
		cQueryCT2C += "CT2C.CT2_ITEMC IN(" + cFiliais + ") AND "
		cQueryCT2C += "ISNULL(ZB4C.D_E_L_E_T_,' ') <> '*'"
		
		//----- CONCATENA AS QUERYS E A CLAUSULA GROUP BY
		
		cQuery	+=	cQueryCT2S
		cQuery	+= "FROM (" + CRLF
		cQuery	+=	cQueryCT2D
		cQuery	+=	"UNION ALL" + CRLF
		cQuery	+=	cQueryCT2C
		cQuery	+=	") CT2S" + CRLF
		
		cQuery	+=	"GROUP BY " + CRLF
		cQuery	+=	"CT2S.POS0001, " + CRLF
		cQuery	+=	"CT2S.POS0007, " + CRLF
		cQuery	+=	"CT2S.POS0017, " + CRLF
		cQuery	+=	"CT2S.POS0057, " + CRLF
		cQuery	+=	"CT2S.POS0058, " + CRLF
		cQuery	+=	"CT2S.POS0076, " + CRLF
		cQuery	+=	"CT2S.POS0084, " + CRLF
		cQuery	+=	"CT2S.POS0120, " + CRLF
		cQuery	+=	"CT2S.POS0184, " + CRLF
		cQuery	+=	"CT2S.POS0185, " + CRLF
		cQuery	+=	"CT2S.POS0213 " + CRLF
		
		cQueryBkp	:=	cQuery
		
		cQuery := ChangeQuery(cQuery)
		
		If TcSqlExec(cQuery) < 0
			
			U_SYNCMAIL(AllTrim(aSyncEnv[15]),cLayout + ' - ' + AllTrim(aSyncEnv[17]),'Erro na execucao da query no banco de dados: ' + Chr(13) + Chr(10) + AllTrim(TcSqlError()),AllTrim(SubStr(aSyncEnv[4],1,6)),AllTrim(SubStr(aSyncEnv[4],7,6)),1,aSyncEnv[8],aSyncEnv[9])
			
		Else
			
			DbUseArea(.T.,"TOPCONN",TCGenQry(,,cQuery),cTrab,.F.,.T.)
			
			U_SYNCLOG(;
				'Agenda : ' +  SubStr(aSyncEnv[4],1,6) + CRLF +;
				'Tarefa : ' +  SubStr(aSyncEnv[4],7,6) + CRLF +;
				cLayout + ' - ' + AllTrim(aSyncEnv[17]) + CRLF +;
				'Query executada com sucesso: ' + CRLF + cQueryBkp)
			
			// Cria arquivo temporario
			
			fErase(Separa(cTempFile,'\',.T.)[3])
			DbCreate(cTempFile,aStru,__LocalDriver)
			DbUseArea(.T.,__LocalDriver,cTempFile,cTempTrab,.T.,.F.)
			
			U_SYNCLOG(;
				'Agenda : ' +  SubStr(aSyncEnv[4],1,6) + CRLF +;
				'Tarefa : ' +  SubStr(aSyncEnv[4],7,6) + CRLF +;
				'Arquivo Temporario criado com sucesso : ' + cTempFile)
			
			// --------------------------------------------------------------
			// Preenchimento do arquivo temporario
			// --------------------------------------------------------------
			
			(cTrab)->(DbGotop())
			
			Do While .Not. (cTrab)->(EOF())
				
				RecLock(cTempTrab,.T.)
				
				(cTempTrab)->POS0001 := cPriDiaMes
				(cTempTrab)->POS0007 := U_SYNCFIL(aSyncEnv[7],5,AllTrim((cTrab)->POS0007))
				(cTempTrab)->POS0017 := U_SYNCFIL(aSyncEnv[7],5,AllTrim((cTrab)->POS0017)) + (cTrab)->POS0084
				(cTempTrab)->POS0057 := (cTrab)->POS0057
				(cTempTrab)->POS0058 := U_SYNCFIL(aSyncEnv[7],2,AllTrim((cTrab)->POS0058)) + U_SYNCFIL(aSyncEnv[7],3,AllTrim((cTrab)->POS0058))
				(cTempTrab)->POS0076 := cPriDiaMes
				(cTempTrab)->POS0084 := (cTrab)->POS0084
				(cTempTrab)->POS0099 := '+' + StrTran(Transform(Abs((cTrab)->POS0099),'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0120 := (cTrab)->POS0120
				(cTempTrab)->POS0121 := '+' + StrTran(Transform(Abs((cTrab)->POS0121),'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0142 := '+' + StrTran(Transform(Abs((cTrab)->POS0142),'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0163 := '+' + StrTran(Transform(Abs((cTrab)->POS0163),'@E '+ Replicate('9',17) + '.' + Replicate('9',2)),' ','0')
				(cTempTrab)->POS0184 := (cTrab)->POS0184
				(cTempTrab)->POS0185 := (cTrab)->POS0185
				(cTempTrab)->POS0213 := (cTrab)->POS0213
				
				MsUnLock()
				
				nCount++
				
				(cTrab)->(DbSkip())
				
			End Do
			
			// --------------------------------------------------------------
			
			U_SYNCDAT(cTempTrab,aSyncEnv,nCount)
			
			(cTrab)->(DbCloseArea())
			(cTempTrab)->(DbCloseArea())
			fErase(Separa(cTempFile,'\',.T.)[3])
			
		End IF
		
	Else
		
		U_SYNCLOG(;
			'Agenda : ' +  SubStr(aSyncEnv[4],1,6) + CRLF +;
			'Tarefa : ' +  SubStr(aSyncEnv[4],7,6) + CRLF +;
			'Nao foi possivel montar o ambiente de geracao do layout ' + cLayout)
		
	End IF
	
	RpcClearEnv()
	
Return
